/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import pojo.ChuyenBayPOJO;

/**
 *
 * @author b2
 */
public class ChuyenBayDAO {

    public static ArrayList<ChuyenBayPOJO> LayDanhSachChuyenBay() {
        ArrayList<ChuyenBayPOJO> ds = new ArrayList<ChuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM CHUYENBAY";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                ChuyenBayPOJO cb = new ChuyenBayPOJO();
                cb.setMaChuyenBay(rs.getString("MaChuyenBay"));
                cb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                cb.setNgayGio(rs.getString("NgayGio"));
                cb.setThoiGianBay(rs.getInt("ThoiGianBay"));
                cb.setSLG1(rs.getInt("SoLuongGheHang1"));
                cb.setSLG2(rs.getInt("SoLuongGheHang2"));

                ds.add(cb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ChuyenBayPOJO LayChuyenBay(String maChuyenBay) {
        ChuyenBayPOJO cb = new ChuyenBayPOJO();
        try {
            String sql = "SELECT * FROM CHUYENBAY WHERE MACHUYENBAY = '" + maChuyenBay + "'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                
                //pb.setMaPhongBan(rs.getInt("MaPhongBan"));
                //pb.setTenPhongBan(rs.getString("TenPhongBan"));
                cb.setMaChuyenBay(rs.getString("MaChuyenBay"));
                cb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                cb.setNgayGio(rs.getString("NgayGio"));
                cb.setThoiGianBay(rs.getInt("ThoiGianBay"));
                cb.setSLG1(rs.getInt("SoLuongGheHang1"));
                cb.setSLG2(rs.getInt("SoLuongGheHang2"));
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return cb;
    }
    
    // phương thức thêm 

    public static boolean themChuyenBay(ChuyenBayPOJO cb) {
        boolean kq = false;
        String sql = String.format("INSERT INTO CHUYENBAY (`MaChuyenBay`,`MaTuyenBay`,`NgayGio`,`ThoiGianBay`,`SoLuongGheHang1`,`SoLuongGheHang2`)VALUES ('%s' ,'%s' ,'%s' ,'%s' ,'%s' , '%s');", cb.getMaChuyenBay(), cb.getMaTuyenBay(), cb.getNgayGio(), cb.getThoiGianBay(), cb.getSLG1(), cb.getSLG2());
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();

        return kq;
    }

    public static ArrayList<ChuyenBayPOJO> TimKiemChuyenBayTheoTen(String machuyenbay) {
        ArrayList<ChuyenBayPOJO> ds = new ArrayList<ChuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM CHUYENBAY WHERE MACHUYENBAY LIKE '%" + machuyenbay + "%'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                ChuyenBayPOJO cb = new ChuyenBayPOJO();
                cb.setMaChuyenBay(rs.getString("MaChuyenBay"));
                cb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                cb.setNgayGio(rs.getString("NgayGio"));
                cb.setThoiGianBay(rs.getInt("ThoiGianBay"));
                cb.setSLG1(rs.getInt("SoLuongGheHang1"));
                cb.setSLG2(rs.getInt("SoLuongGheHang2"));
                ds.add(cb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
     public static ArrayList<ChuyenBayPOJO> TimKiemChuyenBayTheoTB(String maTuyenbay) {
        ArrayList<ChuyenBayPOJO> ds = new ArrayList<ChuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM CHUYENBAY WHERE MATUYENBAY LIKE '%" + maTuyenbay+ "%'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                ChuyenBayPOJO cb = new ChuyenBayPOJO();
                cb.setMaChuyenBay(rs.getString("MaChuyenBay"));
                cb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                cb.setNgayGio(rs.getString("NgayGio"));
                cb.setThoiGianBay(rs.getInt("ThoiGianBay"));
                cb.setSLG1(rs.getInt("SoLuongGheHang1"));
                cb.setSLG2(rs.getInt("SoLuongGheHang2"));
                ds.add(cb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    public static ArrayList<ChuyenBayPOJO> TimKiemChuyenBay(String date1,String date2) {
        ArrayList<ChuyenBayPOJO> ds = new ArrayList<ChuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM CHUYENBAY WHERE  NGAYGIO BETWEEN '" + date1 + "' AND '" + date2 + "'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                ChuyenBayPOJO cb = new ChuyenBayPOJO();
                cb.setMaChuyenBay(rs.getString("MaChuyenBay"));
                cb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                cb.setNgayGio(rs.getString("NgayGio"));
                cb.setThoiGianBay(rs.getInt("ThoiGianBay"));
                cb.setSLG1(rs.getInt("SoLuongGheHang1"));
                cb.setSLG2(rs.getInt("SoLuongGheHang2"));
                ds.add(cb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    public static ArrayList<ChuyenBayPOJO> TimKiemChuyenBay(Date date1,Date date2) {
        ArrayList<ChuyenBayPOJO> ds = new ArrayList<ChuyenBayPOJO>();
        try {
            String sql = "SELECT * FROM CHUYENBAY WHERE  NGAYGIO BETWEEN #" + date1 + "# AND #" + date2 + "#";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                ChuyenBayPOJO cb = new ChuyenBayPOJO();
                cb.setMaChuyenBay(rs.getString("MaChuyenBay"));
                cb.setMaTuyenBay(rs.getString("MaTuyenBay"));
                cb.setNgayGio(rs.getString("NgayGio"));
                cb.setThoiGianBay(rs.getInt("ThoiGianBay"));
                cb.setSLG1(rs.getInt("SoLuongGheHang1"));
                cb.setSLG2(rs.getInt("SoLuongGheHang2"));
                ds.add(cb);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    // phương thức xóa 

    public static boolean xoaChuyenBay(String maChuyenBay) {
        boolean kq = false;
        String sql = "DELETE FROM CHUYENBAY WHERE MACHUYENBAY = '" + maChuyenBay + "'";
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();
        return kq;
    }
    public static boolean themTinhTrangVe(String maChuyenbay,int soGheTrong,int soGheDat){
        boolean kq = false;
        String sql = String.format("INSERT INTO TINHTRANGVE (`MaChuyenBay`,`SoGheTrong`,`SoGheDat`) VALUES ('%s','%s','%s')", maChuyenbay,soGheTrong,soGheDat);
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();
        return kq;
    
    }
    
    public static boolean xoaTinhTrangVe(String maChuyenBay){
        boolean kq = false;
        String sql = "DELETE FROM TINHTRANGVE WHERE MACHUYENBAY = '" + maChuyenBay + "'";
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();
        return kq;
    
    }

    public static int soGheTrong(String maChuyenBay) {
        int kq = 0;
        try {

            String sql = "SELECT * FROM TINHTRANGVE WHERE MACHUYENBAY = '" + maChuyenBay + "'";
            MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                kq = rs.getInt("SoGheTrong");
            }
            helper.close();

        } catch (SQLException ex) {
            Logger.getLogger(ChuyenBayDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return kq;
    }

    public static boolean updateSoLuongGhe(String maChuyenBay, int soGhe) {

        boolean kq = false;
        String sql = "UPDATE TINHTRANGVE SET SOGHETRONG = '" + soGhe + "' WHERE MACHUYENBAY = '" + maChuyenBay + "'";
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();
        return kq;
    }
    public static boolean updateSoLuongGheDat(String maChuyenBay, int soGhe) {

        boolean kq = false;
        String sql = "UPDATE TINHTRANGVE SET SoGheDat = '" + soGhe + "' WHERE MACHUYENBAY = '" + maChuyenBay + "'";
        MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
        helper.open();
        int n = helper.executeUpdate(sql);
        if (n == 1) {
            kq = true;
        }
        helper.close();
        return kq;
    }
}
